"""数据库操作模块"""

import sqlite3
import os
from datetime import datetime
from typing import List, Optional, Tuple, Any
from dataclasses import dataclass
from pathlib import Path


@dataclass
class ClipboardItem:
    """剪切板数据项"""
    id: int
    content: str
    content_type: str  # 'text', 'image', 'files'
    timestamp: datetime
    size: int = 0
    
    def __post_init__(self):
        if isinstance(self.timestamp, str):
            self.timestamp = datetime.fromisoformat(self.timestamp)


class ClipboardDatabase:
    """剪切板数据库管理类"""
    
    def __init__(self, db_path: Optional[str] = None):
        """初始化数据库连接"""
        if db_path is None:
            # 使用用户数据目录
            app_data_dir = Path.home() / ".smart_clipboard"
            app_data_dir.mkdir(exist_ok=True)
            db_path = app_data_dir / "clipboard.db"
        
        self.db_path = str(db_path)
        print(f"数据库路径: {self.db_path}")
        self.init_database()
    
    def init_database(self):
        """初始化数据库表结构"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS clipboard_items (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    content TEXT NOT NULL,
                    content_type TEXT NOT NULL DEFAULT 'text',
                    timestamp DATETIME NOT NULL,
                    size INTEGER DEFAULT 0,
                    UNIQUE(content, content_type)
                )
            ''')
            
            # 创建索引以提高查询性能
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_timestamp 
                ON clipboard_items(timestamp DESC)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_content_type 
                ON clipboard_items(content_type)
            ''')
            
            conn.commit()
    
    def add_item(self, content: str, content_type: str = 'text') -> Optional[int]:
        """添加剪切板项目"""
        if not content or not content.strip():
            return None
            
        size = len(content.encode('utf-8'))
        timestamp = datetime.now()
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 检查是否已存在相同内容
            cursor.execute('''
                SELECT id FROM clipboard_items 
                WHERE content = ? AND content_type = ?
            ''', (content, content_type))
            
            existing = cursor.fetchone()
            if existing:
                # 更新现有项目的时间戳，使其成为最新的
                cursor.execute('''
                    UPDATE clipboard_items 
                    SET timestamp = ? 
                    WHERE id = ?
                ''', (timestamp, existing[0]))
                conn.commit()
                return existing[0]
            else:
                # 添加新项目
                cursor.execute('''
                    INSERT INTO clipboard_items (content, content_type, timestamp, size)
                    VALUES (?, ?, ?, ?)
                ''', (content, content_type, timestamp, size))
                conn.commit()
                return cursor.lastrowid
    
    def get_recent_items(self, limit: int = 20, offset: int = 0) -> List[ClipboardItem]:
        """获取最近的剪切板项目"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT id, content, content_type, timestamp, size
                FROM clipboard_items
                ORDER BY timestamp DESC
                LIMIT ? OFFSET ?
            ''', (limit, offset))
            
            items = []
            for row in cursor.fetchall():
                items.append(ClipboardItem(
                    id=row[0],
                    content=row[1],
                    content_type=row[2],
                    timestamp=datetime.fromisoformat(row[3]),
                    size=row[4]
                ))
            
            return items
    
    def search_items(self, search_text: str, limit: int = 20, offset: int = 0) -> List[ClipboardItem]:
        """搜索剪切板项目"""
        if not search_text or not search_text.strip():
            return self.get_recent_items(limit, offset)
        
        search_pattern = f"%{search_text.strip()}%"
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT id, content, content_type, timestamp, size
                FROM clipboard_items
                WHERE content LIKE ? OR content_type LIKE ?
                ORDER BY timestamp DESC
                LIMIT ? OFFSET ?
            ''', (search_pattern, search_pattern, limit, offset))
            
            items = []
            for row in cursor.fetchall():
                items.append(ClipboardItem(
                    id=row[0],
                    content=row[1],
                    content_type=row[2],
                    timestamp=datetime.fromisoformat(row[3]),
                    size=row[4]
                ))
            
            return items
    
    def get_item_by_id(self, item_id: int) -> Optional[ClipboardItem]:
        """根据ID获取剪切板项目"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT id, content, content_type, timestamp, size
                FROM clipboard_items
                WHERE id = ?
            ''', (item_id,))
            
            row = cursor.fetchone()
            if row:
                return ClipboardItem(
                    id=row[0],
                    content=row[1],
                    content_type=row[2],
                    timestamp=datetime.fromisoformat(row[3]),
                    size=row[4]
                )
            return None
    
    def move_to_top(self, item_id: int) -> bool:
        """将指定项目移到最前面（更新时间戳）"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE clipboard_items 
                SET timestamp = ? 
                WHERE id = ?
            ''', (datetime.now(), item_id))
            conn.commit()
            return cursor.rowcount > 0
    
    def delete_item(self, item_id: int) -> bool:
        """删除指定项目"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('DELETE FROM clipboard_items WHERE id = ?', (item_id,))
            conn.commit()
            return cursor.rowcount > 0
    
    def clear_old_items(self, keep_count: int = 100):
        """清理旧的剪切板项目，保留最新的指定数量"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                DELETE FROM clipboard_items 
                WHERE id NOT IN (
                    SELECT id FROM clipboard_items 
                    ORDER BY timestamp DESC 
                    LIMIT ?
                )
            ''', (keep_count,))
            conn.commit()
    
    def get_stats(self) -> dict:
        """获取数据库统计信息"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 总项目数
            cursor.execute('SELECT COUNT(*) FROM clipboard_items')
            total_count = cursor.fetchone()[0]
            
            # 按类型统计
            cursor.execute('''
                SELECT content_type, COUNT(*) 
                FROM clipboard_items 
                GROUP BY content_type
            ''')
            type_stats = dict(cursor.fetchall())
            
            # 数据库大小
            db_size = os.path.getsize(self.db_path) if os.path.exists(self.db_path) else 0
            
            return {
                'total_items': total_count,
                'type_stats': type_stats,
                'db_size': db_size,
                'db_path': self.db_path
            } 